/**
 * Copyright 2011 Google Inc.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package com.google.api.adwords.starterkit.db.tables;

import com.google.api.adwords.starterkit.db.keys.GeoTargetKey;
import com.google.api.adwords.starterkit.db.structures.GeoTargetStructure;
import com.google.api.adwords.starterkit.utils.DumpExceptions;
import com.google.api.adwords.starterkit.wrappers.CampaignWrapper;

import org.apache.log4j.Logger;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;

/**
 * The Class GeoTargetTable encapsulates most of GeoTarget DB access.
 * 
 * @author rodrigo.f.vale@gmail.com  (Rodrigo Vale)
 */
public class GeoTargetTable {
  private PreparedStatement prepReplace;
  private PreparedStatement prepLoad;
  private PreparedStatement prepDel;
  private Connection conn;
  private static final Logger log = Logger.getLogger("StarterKit");
  
  public void initTable() {
    try { 
      Statement stat = conn.createStatement();      
      stat.executeUpdate(
            "CREATE TABLE IF NOT EXISTS GeoTargets "
          + "   ("
          + "     LocalCampaignId INTEGER, "
          + "     CampaignId      LONG, "
          + "     Country         STRING default '', "
          + "     Province        STRING default '', "
          + "     City            STRING default '', "
          + "     Exclude         BOOLEAN, "
          + "     PRIMARY KEY (LocalCampaignId, Country, Province, City)"
          + "   ); ");
      stat.executeUpdate(
            "CREATE INDEX IF NOT EXISTS GeoTargetsLocalCampaignIdIndex "
          + "ON GeoTargets(LocalCampaignId);");
      stat.executeUpdate(
            "CREATE INDEX IF NOT EXISTS GeoTargetsCampaignIdIndex "
          + "ON GeoTargets (CampaignId);");
      
      prepLoad = conn.prepareStatement(
          "SELECT * FROM GeoTargets WHERE LocalCampaignId = ?;"); 

      prepReplace = conn.prepareStatement(
          "REPLACE INTO GeoTargets (LocalCampaignId, CampaignId, Country," 
          + "Province, City, Exclude) VALUES (?, ?, ?, ?, ? ,?);");

      prepDel = conn.prepareStatement(
          "DELETE FROM GeoTargets WHERE LocalCampaignId = ? ");              
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }        
  }

  /**
   * Instantiates a new GeoTargetTable.
   *
   * @param conn the DB connection
   */
  public GeoTargetTable(Connection conn) {
    this.conn = conn;
    initTable();
  }
  
  /**
   * Convert the ResultSet format from DB into GeoTargetStructure 
   *
   * @param rs the ResultSet assigned to GeoTarget table
   * @return the database row in GeoTargetStructure format 
   */
  public GeoTargetStructure getResultSet(ResultSet rs) {
    GeoTargetStructure geoTargetStructure = null;
    try {
      geoTargetStructure = new GeoTargetStructure(rs.getInt("LocalCampaignId"), 
          rs.getLong("CampaignId"), rs.getString("City"), 
          rs.getString("Province"), rs.getString("Country"), 
          rs.getBoolean("Exclude"));
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
    return geoTargetStructure;    
  }
  
  /**
   * Gets all tuples from DB that has the local campaign id defined at
   * geoTargetKey   
   *
   * @param geoTargetKey the GeoTargetKey that defines the local campaign id
   * @return the database row in GeoTargetStructure format 
   */
  public HashSet<GeoTargetStructure> get(GeoTargetKey geoTargetKey) {
    HashSet<GeoTargetStructure> geoTargets = new HashSet<GeoTargetStructure>();
    ResultSet rs = null;
    try {
      prepLoad.setInt(1, geoTargetKey.getLocalCampaignId());
      rs = prepLoad.executeQuery();
      while (rs.next()) {
        GeoTargetStructure geoTargetStructure = getResultSet(rs);
        geoTargets.add(geoTargetStructure);
      }
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }  
    return geoTargets;
  }
  
  /**
   * Save a geoTargets into DB
   *
   * @param geoTargets the geoTargets to be saved
   */
  public void save(HashSet<GeoTargetStructure> geoTargets) {        
    for (Iterator<GeoTargetStructure> i = geoTargets.iterator(); i.hasNext(); ) 
    {
      GeoTargetStructure geoTargetStructure = i.next();
      try {
        prepReplace.setInt(1, geoTargetStructure.getLocalCampaignId());
        prepReplace.setLong(2, geoTargetStructure.getCampaignId());
        prepReplace.setString(3, geoTargetStructure.getCountryCode());
        prepReplace.setString(4, geoTargetStructure.getProvinceCode());
        prepReplace.setString(5, geoTargetStructure.getCityName());
        prepReplace.setBoolean(6, geoTargetStructure.getExclude());
        prepReplace.executeUpdate();
      } catch (SQLException e) {
        DumpExceptions.dumpSQLException(e);
      }         
    }
  }

  /**
   * Save a geoTargets into DB
   *
   * @param geoTargets the geoTargets to be saved
   */
  public void saveBatch(GeoTargetStructure geoTargetStructure) {        
    try {
      prepReplace.setInt(1, geoTargetStructure.getLocalCampaignId());
      prepReplace.setLong(2, geoTargetStructure.getCampaignId());
      prepReplace.setString(3, geoTargetStructure.getCountryCode());
      prepReplace.setString(4, geoTargetStructure.getProvinceCode());
      prepReplace.setString(5, geoTargetStructure.getCityName());
      prepReplace.setBoolean(6, geoTargetStructure.getExclude());
      prepReplace.addBatch();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }         
  }  
  
  public void executeBatch() {
    try {
      prepReplace.executeBatch();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
  }  
  
  /**
   * Delete a geoTarget from DB
   *
   * @param geoTargetKey the geoTarget to be deleted
   */
  public void del(GeoTargetKey geoTargetKey) {
    try {
      prepDel.setLong(1, geoTargetKey.getLocalCampaignId());
      prepDel.execute();      
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }       
  }  
  
  /**
   * Get all campaign ids from AdWords associated to campaigns just created 
   * by Starter Kit
   *
   * @return a map between campaign id and local campaign id 
   */
  public HashMap<Integer, Long> getNewCampaignsIds() {
    HashMap<Integer, Long> campaignsIds = new HashMap<Integer, Long>(); 
    ResultSet rs = null;
    try {
      Statement stat = conn.createStatement();
      rs = stat.executeQuery(
            "SELECT DISTINCT ca.localId, ca.CampaignId FROM Campaigns ca, "
          + "GeoTargets gt WHERE gt.LocalCampaignId = ca.LocalId AND "
          + "gt.CampaignId = 0;");
      while (rs.next()) {
        campaignsIds.put(rs.getInt("localId"), rs.getLong("CampaignId"));
      }
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }  
    return campaignsIds;
  }

  /**
   * Update campaigns ids at geo target table.
   */
  public void updateCampaignsIds() {
    log.debug("Updating CampaignId at GeoTargets table");
    HashMap<Integer, Long> newCampaignsId = getNewCampaignsIds();
    PreparedStatement prep = null;
    try {
      prep = conn.prepareStatement(
            "UPDATE GeoTargets SET CampaignId = ? WHERE LocalCampaignId = ? "
          + "AND CampaignId = 0");
      int count = 0;
      for (int localCampaignId : newCampaignsId.keySet()) {
        long campaignId = newCampaignsId.get(localCampaignId);
        prep.setLong(1, campaignId);
        prep.setInt(2, localCampaignId);
        prep.addBatch();
        count++;
        if (count == 100000) {
          count = 0;
          prep.executeBatch();
        }
      }    
      prep.executeBatch();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (prep != null) {
        try {
          prep.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }     
  }
  
  /**
   * Return all GeoTargets modified during the last Starter Kit execution
   * 
   * @return the ResultSet that contains GeoTargets modified 
   */
  public ResultSet getModified() {
    PreparedStatement prep = null;
    ResultSet rs = null;
    try {
      prep = conn.prepareStatement(
            "SELECT gt.CampaignId CampaignId, gt.Country Country, " 
          + "gt.Province Province, gt.City City, gt.Exclude Exclude " 
          + "FROM GeoTargets gt, Campaigns ca WHERE ca.IsGeoModified = 1 " 
          + "AND ca.LocalId = gt.LocalCampaignId");
      rs = prep.executeQuery();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }    
    return rs;
  }
  
  /**
   * Reset all campaigns status flags to its default values
   */
  public void resetStatus() {
    PreparedStatement prep = null;
    try {
      prep = conn.prepareStatement(
            "UPDATE Campaigns SET IsGeoModified = 0;");
      prep.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (prep != null) {
        try {
          prep.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }     
  }

  /**
   * Convert a set of GeoTargetSettings structure into a set of Strings 
   *
   * @param geos A set of getTargetting converted to a String
   * @param geoTargets the original GeoTargetSettings 
   */
  private static void convert(HashSet<String> geos, 
      HashSet<GeoTargetStructure> geoTargets) {
    for (Iterator<GeoTargetStructure> i = geoTargets.iterator(); i.hasNext(); ) 
    {
      GeoTargetStructure geoTarget = i.next();
      String key = geoTarget.getCityName().toLowerCase() + "#" 
          + geoTarget.getCountryCode().toLowerCase() + "#" 
          + geoTarget.getProvinceCode().toLowerCase() + "#" 
          + geoTarget.getExclude();
      geos.add(key);
    }
  }
  
  /**
   * Verifies if two GeoTargetList are equals
   *
   * @param a the GeoTargetList a
   * @param b the GeoTargetList b
   * @return true, if equals
   */
  public static boolean equals(HashSet<GeoTargetStructure> a, 
      HashSet<GeoTargetStructure> b) {
    HashSet<String> geoA = new HashSet<String>();
    HashSet<String> geoB = new HashSet<String>();
    
    convert(geoA, a);
    convert(geoB, b);
    
    for (Iterator<String> i = geoA.iterator(); i.hasNext(); ) {
      String key = i.next();
      if (!geoB.contains(key)) {
        return false;
      }
    }    

    for (Iterator<String> i = geoB.iterator(); i.hasNext(); ) {
      String key = i.next();
      if (!geoA.contains(key)) {
        return false;
      }
    }
    return true;
  }
  
  /**
   * Links a set of GeoTarget to a campaign defined at CampaignWrapper
   *
   * @param campaignWrapper the campaignWrapper representing a campaign
   * @param geoTargets a set of Geotarget to be linked to the campaign
   */
  public static void link(CampaignWrapper campaignWrapper, 
      HashSet<GeoTargetStructure> geoTargets) {
    for (Iterator<GeoTargetStructure> i 
        = geoTargets.iterator(); i.hasNext(); ) {
      GeoTargetStructure geoTargetStructure = i.next();
      geoTargetStructure.setCampaignId(campaignWrapper.getCampaignId());
      geoTargetStructure.setLocalCampaignId(campaignWrapper.getLocalId());
    }        
  }
}
